Concatenated Cluster Keys

Oracle permits up to 16 columns in the cluster key of a hash cluster. However, to perform a Hash Cluster Scan, a SQL must include equals predicates on all key columns in separate AND clauses.

Consider the following examples on table trans_hist, which is clustered on cust_id and trans_mth:

SELECT *
FROM trans_hist
WHERE cust_id = 1001
AND   trans_mth = 200506

SELECT *
FROM trans_hist
WHERE cust_id = 1001
AND   trans_mth BETWEEN 200506 AND 200508

SELECT /*+ USE_CONCAT*/ *
FROM trans_hist
WHERE cust_id = 1001
AND   trans_mth IN (200506, 200507, 200508)

SELECT *
FROM trans_hist
WHERE cust_id = 1001

The first example satisfies all conditions for performing a hash cluster scan.

The second example uses trans_mth in a range comparison (BETWEEN), so it will not perform a cluster scan. Note that if the cluster was an Index Cluster, or the same two columns were simply indexed, then Oracle would be able to perform an Index scan.

If trans_mth can contain only discrete integer values, then the third example is the same as the second, except Oracle is able to perform a hash cluster scan.

The fourth example does not use the entire cluster key, so it cannot perform a hash cluster scan

As these exapmles demonstrate, hash clusters are far less versatile than indexes. Read the Oracle documentation carefully before you implement a hash cluster, as indexes may be more appropriate to your situation.